﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Security.DataLayer
{
    /// <summary>
    /// Помогает получить информацию об системных обьектах SqlServer,
    /// а также добавить и удалить их
    /// </summary>
    internal class ServerInfoHelper
    {
        private const string getLoginsQuery = "SELECT name FROM sys.server_principals WHERE type = 'S' AND principal_id > 1";
        private const string getUsersQuery = "SELECT name FROM sys.database_principals WHERE type = 'S' AND (sid > 0) AND (sid IS NOT NULL)";
        private const string addLoginQuery = "sp_addlogin";
        private const string addUserQuery = "sp_adduser";
        private const string delUserQuery = "sp_dropuser";
        private const string delLoginQuery = "sp_droplogin";
        private const string addRoleMemberQuery = "exec sp_addrolemember @role = '{0}', @membername = '{1}'";
        private const string dropRoleMemberQuery = "exec sp_droprolemember @role = '{0}', @membername = '{1}'";
        private const string loginParameter = "@loginame";
        private const string passwordParameter = "@passwd";
        private const string databaseParameter = "@defdb";
        private const string userParameter = "@name_in_db";
        private const string defaultLanguage = "@deflanguage";

        private readonly SqlConnection connection;

        public ServerInfoHelper(SqlConnection connection)
        {
            this.connection = connection;
        }

        /// <summary>
        /// Возвращает имена пользователей на сервере
        /// </summary>
        /// <returns></returns>
        public IEnumerable<string> Logins()
        {
            using (var command = connection.CreateCommand())
            {
                command.CommandText = getLoginsQuery;
                command.CommandType = CommandType.Text;

                using (var reader = command.ExecuteReader())
                    if (reader != null)
                        while (reader.Read())
                            yield return (string)reader["name"];
            }
        }

        /// <summary>
        /// Возвращает пользователей в текущей БД
        /// </summary>
        /// <returns></returns>
        internal IEnumerable<string> Users()
        {
            var users = new List<string>();
            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = getUsersQuery;

                using (var reader = command.ExecuteReader())
                    if (reader != null)
                        while (reader.Read())
                            yield return (string)reader["name"];
            }
        }

        /// <summary>
        /// Выполняет команду DROP USER в контексте текущего подключения
        /// </summary>
        /// <param name="user"></param>
        internal void DropUser(string user)
        {
            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = delUserQuery; //string.Format(delUserQuery, userParameter);
                command.Parameters.AddRange(new[]
                                                {
                                                    new SqlParameter(userParameter, user)
                                                });

                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Создает пользователя <paramref name="user"/> в текущей БД для логина <paramref name="login"/>
        /// </summary>
        internal void CreateUser(string user, string login)
        {
            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = addUserQuery; //string.Format(addUserQuery, userParameter, loginParameter);
                command.Parameters.AddRange(new[]
                                                {
                                                    new SqlParameter(userParameter, user),
                                                    new SqlParameter(loginParameter, login)
                                                });
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Удаляет логин <paramref name="login"/>  для текущего сервера
        /// </summary>
        internal void DropLogin(string login)
        {
            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = delLoginQuery;
                command.Parameters.AddRange(new[]
                                                {
                                                    new SqlParameter(loginParameter, login)
                                                });
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Создает логин <paramref name="login"/> с паролем <paramref name="password"/> 
        /// с текущей базой данных по умолчанию
        /// и русским языком по умолчанию
        /// </summary>
        internal void CreateLogin(string login, string password)
        {
            var database = connection.Database;
            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = addLoginQuery;// string.Format(addLoginQuery, loginParameter, passwordParameter, databaseParameter, defaultLanguage);
                command.Parameters.AddRange(new[]
                                                {
                                                    new SqlParameter(loginParameter, login),
                                                    new SqlParameter(passwordParameter, password),
                                                    new SqlParameter(databaseParameter, database),
                                                    new SqlParameter(defaultLanguage, "Russian")
                                                });
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Добавляет пользователя <paramref name="user"/> к роли в БД <paramref name="role"/>
        /// </summary>
        internal void AddUserToDatabaseRole(string role, string user)
        {
            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = string.Format(addRoleMemberQuery, user, role);
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Удаляет пользователя <paramref name="user"/> из роли <paramref name="role"/>
        /// </summary>
        internal void RemoveUserFromDatabaseRole(string role, string user)
        {
            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = string.Format(dropRoleMemberQuery, role, user);
                command.ExecuteNonQuery();
            }
        }
    }
}
